package com.example.demo.util;

import com.example.demo.entity.User;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

public class ExcelUtil {

    public static List<User> parseExcelFile(MultipartFile file) throws IOException {
        List<User> users = new ArrayList<>();
        try (InputStream inputStream = file.getInputStream()) {
            Workbook workbook = WorkbookFactory.create(inputStream);
            Sheet sheet = workbook.getSheetAt(0);

            boolean isHeader = true;
            for (Row row : sheet) {
                if (isHeader) {
                    isHeader = false;
                    continue; // 跳过表头
                }

                User user = parseUserFromRow(row);
                if (user != null) {
                    users.add(user);
                }
            }
        }
        return users;
    }

    private static User parseUserFromRow(Row row) {
        try {
            String authId = getCellValue(row.getCell(0));
            String userName = getCellValue(row.getCell(1));
            String email = getCellValue(row.getCell(2));
            String deptName = getCellValue(row.getCell(3));
            String userType = getCellValue(row.getCell(4));
            String password = getCellValue(row.getCell(5)); // 可选

            if (authId == null || authId.trim().isEmpty()) {
                return null; // 必填字段缺失
            }

            User user = new User();
            user.setAuthId(authId);
            user.setUserName(userName != null ? userName : "未知用户");
            user.setEmail(email);
            user.setDeptName(deptName);
            user.setUserType(userType);
            user.setPassword(password != null ? password : "123456"); // 默认密码

            return user;
        } catch (Exception e) {
            throw new RuntimeException("解析第 " + (row.getRowNum() + 1) + " 行失败", e);
        }
    }

    private static String getCellValue(Cell cell) {
        if (cell == null) return null;
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf((long) cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            default:
                return null;
        }
    }

    /**
     * 导出用户数据到Excel
     * @param users 用户列表
     * @return Excel文件的字节数组
     * @throws IOException IO异常
     */
    public static byte[] exportUsersToExcel(List<User> users) throws IOException {
        try (Workbook workbook = new XSSFWorkbook();
             ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
            
            Sheet sheet = workbook.createSheet("用户数据");
            
            // 创建表头
            createHeaderRow(sheet);
            
            // 创建数据行
            for (int i = 0; i < users.size(); i++) {
                createUserRow(sheet, users.get(i), i + 1);
            }
            
            // 自动调整列宽
            for (int i = 0; i < 6; i++) {
                sheet.autoSizeColumn(i);
            }
            
            workbook.write(outputStream);
            return outputStream.toByteArray();
        }
    }
    
    /**
     * 创建Excel表头
     */
    private static void createHeaderRow(Sheet sheet) {
        Row headerRow = sheet.createRow(0);
        
        // 创建表头样式
        CellStyle headerStyle = sheet.getWorkbook().createCellStyle();
        Font headerFont = sheet.getWorkbook().createFont();
        headerFont.setBold(true);
        headerStyle.setFont(headerFont);
        headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        
        String[] headers = {"统一认证号", "用户名", "邮箱", "部门名称", "用户类型", "密码"};
        
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headerStyle);
        }
    }
    
    /**
     * 创建用户数据行
     */
    private static void createUserRow(Sheet sheet, User user, int rowNum) {
        Row row = sheet.createRow(rowNum);
        
        row.createCell(0).setCellValue(user.getAuthId() != null ? user.getAuthId() : "");
        row.createCell(1).setCellValue(user.getUserName() != null ? user.getUserName() : "");
        row.createCell(2).setCellValue(user.getEmail() != null ? user.getEmail() : "");
        row.createCell(3).setCellValue(user.getDeptName() != null ? user.getDeptName() : "");
        row.createCell(4).setCellValue(user.getUserType() != null ? user.getUserType() : "");
        row.createCell(5).setCellValue("******"); // 密码不导出明文
    }
}
